package com.laxcen.smoke.dao;

import com.laxcen.smoke.dto.res.*;
import com.laxcen.smoke.entity.PickCode;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface IssueDao {

    @Insert("insert into issue(id,versionno,versiondes,file,createtime) values(#{id},#{versionno},#{versiondes},#{file},#{createtime})")
    int insert(String id, String versionno, String versiondes, String file, Date createtime);

    //添加到APP升级记录表
    @Insert("insert into issuerecord(id,smartno,versionno,createtime) values(#{id},#{smartno},#{versionno},#{createtime})")
    int insertIssueRecord(String id, String smartno, String versionno, Date createtime);

    //添加到APP升级状态表
    @Insert("insert into issuestatus(id,smartno,newversion,localversion,status,createtime) values(#{id},#{smartno},#{newversion},#{localversion},#{status},#{createtime})")
    int insertIssueStatus(String id, String smartno, String newversion, String localversion, int status, Date createtime);

    //更新到APP升级状态表
    @Update("update issuestatus set status = #{status},newversion = #{newversion},localversion = #{localversion} where smartno = #{smartno}")
    int updateIssueStatus(int status, String newversion, String localversion, String smartno);

    @Update("update issue set versionno = #{versionno},versiondes = #{versiondes},file = #{file} where id = #{id}")
    int updateVersion(String versionno, String versiondes, String file, String id);

    @Update("update smart set versionid = #{versionid} where no = #{smartno}")
    int updateSmartVerOne(String versionid, String smartno);

    //修改智能柜密码(超管)
    @Update("update smart set password = #{password},updatetime = #{updatetime} where no = #{smartno}")
    int updateSmartpassword(String password, Date updatetime, String smartno);

    //修改智能柜密码(普管)
    @Update("update smart set password = #{password},updatetime = #{updatetime} where no = #{smartno} and no in ( select a.no from( select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) a)")
    int updateSmartpasswordRole(String password, Date updatetime, String smartno, String place);

    @Delete("delete from issue where id = #{id}")
    int deleteOne(String id);

    @Select("select file from issue where id = #{id}")
    String getFileById(String id);

//    @Select("select devicename from smart where no = #{smartno}")
//    String getDeviceName(String smartno);

    //查询当前智能柜的版本号
    @Select("select iss.versionno from smart sm right join issue iss on sm.versionid = iss.id where sm.no = #{smartno}")
    String getVersionnoBySmNo(String smartno);

    //查询升级状态表是否有此设备编号
    @Select("select smartno from issuestatus where smartno = #{smartno}")
    String getIssueSmNoBYNo(String smartno);

    @Select("select id from issue where versionno = #{versionno} ")
    String getIdByVerNo(String versionno);

    @Select("select file from issue where id = #{id}")
    String getFileUrlById(String id);

    @Select("select versiondes from issue where id = #{id}")
    String getVersionDesById(String id);

    @Select("select id,versionno,versiondes,createtime from issue order by createtime desc")
    List<AppManageListRes> getListAll();

    @Select("select id,smartno,versionno,createtime from issuerecord")
    List<AppRecordRes> getAppRecordList();

    @Select("select id,smartno,newversion,localversion,status from issuestatus")
    List<AppStatusRes> getAppStatusList();

    @Select("select  id,versionno,versiondes,createtime from issue where createtime between #{starttime} and #{endtime} order by createtime desc")
    List<AppManageListRes> getListByTime(Date starttime, Date endtime);

    @Select("select distinct sm.no smartno,v.versionno versionno,v.createtime createtime from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid left join issue v on sm.versionid = v.id ")
    List<AppUpGradeListRes> getAppUpGradeAllList();

    @Select("select distinct sm.no smartno,v.versionno versionno,v.createtime createtime from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid left join issue v on sm.versionid = v.id where v.versionno = #{versionno} ")
    List<AppUpGradeListRes> getAppUpGradeListByVerNo(String versionno);

    @Select("select distinct sm.no smartno,v.versionno versionno,v.createtime createtime from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid left join issue v on sm.versionid = v.id where v.createtime between #{starttime} and #{endtime} ")
    List<AppUpGradeListRes> getAppUpGradeListByTime(Date starttime, Date endtime);

    @Select("select distinct sm.no smartno,v.versionno versionno,v.createtime createtime from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid left join issue v on sm.versionid = v.id where v.versionno = #{versionno} and v.createtime between #{starttime} and #{endtime} ")
    List<AppUpGradeListRes> getAppUpGradeListByVerNoAndTime(String versionno, Date starttime, Date endtime);


    @Insert("insert into pickcode(id,verifynum,verifymovetime,createtime) values(#{id},#{verifynum},#{verifymovetime},#{createtime})")
    int insertpickcode(String id, int verifynum, double verifymovetime, Date createtime);

    @Select("select * from pickcode order by createtime desc")
    List<PickCode>  getPickCodeList();

    //获取设备编号轮询
    @Select("select distinct sm.no smartno from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId ")
    List<String> getSmartNo();

    //获取智能柜密码列表
    @Select("select distinct sm.id id,sm.no smartno,sm.password password,sm.updatetime updatetime from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid where sm.password is not null group by sm.no order by sm.updatetime desc")
    List<SmartPasswordRes> getSmartPasswordList();

    @Select("select distinct sm.id id,sm.no smartno,sm.password password,sm.updatetime updatetime from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid where sm.password is not null and sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) group by sm.no order by sm.updatetime desc")
    List<SmartPasswordRes> getSmartPasswordListRole(String place);

}
